package com.sa07.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.sa07.exception.DatabaseExecuteFailedException;

/**
 * 默认的Session对象
 * 
 * @author Long.shangbo
 * 
 *         2014-2-15 下午6:48:34
 */
public abstract class DefaultSession implements Session {

	protected static final Logger logger = Logger
			.getLogger(DefaultSession.class);

	/**
	 * 结果集缓存，到时候随着session的关闭全部关闭
	 */
	private List<ResultSet> resultSetCache = new ArrayList<ResultSet>();

	/**
	 * 获得连接的方法
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException {
		return createConnection();
	}

	@Override
	public int executeNoneQuery(String sql, Object... params)
			throws DatabaseExecuteFailedException {
		int result = 0;
		Connection conn = null;
		PreparedStatement preStatement = null;

		try {
			conn = getConnection();
			preStatement = conn.prepareStatement(sql);
			initPreparedStatement(preStatement, params);
			result = preStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Execute Sql(" + sql + ") failed, caused by -> " + e);
			throw new DatabaseExecuteFailedException(e);
		} finally {
			try {
				closeStatement(preStatement);
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("Close statement failed, caused by -> " + e);
				throw new DatabaseExecuteFailedException(e);
			}
		}

		return result;
	}

	/**
	 * 关闭语句执行对象
	 * 
	 * @throws SQLException
	 */
	private void closeStatement(Statement preStatement) throws SQLException {
		if (preStatement != null) {
			preStatement.close();
		}
	}

	/**
	 * 初始化语句执行对象
	 * 
	 * @param params
	 * @throws SQLException
	 */
	private void initPreparedStatement(PreparedStatement preStatement,
			Object... params) throws SQLException {
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				if (params[i] != null) {
					preStatement.setObject(i + 1, params[i]);
				}
			}
		}
	}

	@Override
	public ResultSet executeQuery(String sql, Object... params)
			throws DatabaseExecuteFailedException {
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement preStatement = null;

		try {
			conn = getConnection();
			preStatement = conn.prepareStatement(sql);
			initPreparedStatement(preStatement, params);
			rs = preStatement.executeQuery();
			// 缓存一份
			resultSetCache.add(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Execute Sql(" + sql + ") failed, caused by -> " + e);
			throw new DatabaseExecuteFailedException(e);
		}

		return rs;
	}

	@Override
	public DataTable executeDataTable(String sql, Object... params)
			throws DatabaseExecuteFailedException {
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement preStatement = null;
		DataTable dataTable = null;

		try {
			conn = getConnection();
			preStatement = conn.prepareStatement(sql);
			initPreparedStatement(preStatement, params);
			rs = preStatement.executeQuery();
			// 得到结果集(rs)的结构信息，比如字段数、字段名等
			ResultSetMetaData md = rs.getMetaData();
			// 返回此 ResultSet 对象中的列数
			int columnCount = md.getColumnCount();
			String[] header = new String[columnCount];

			for (int i = 0; i < columnCount; i++) {
				header[i] = md.getColumnName(i);
			}
			dataTable = new DataTable();
			dataTable.setHeader(header);
			List<Object[]> datas = new ArrayList<Object[]>();
			Object[] data = new Object[columnCount];

			while (rs.next()) {
				for (int i = 0; i < columnCount; i++) {
					data[i] = rs.getObject(i + 1);
				}
				datas.add(data);
			}
			dataTable.setDatas(datas);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				closeResultSet(rs);
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("Close ResultSet failed, caused by -> " + e);
				throw new DatabaseExecuteFailedException(e);
			}
		}

		return dataTable;
	}

	/**
	 * 关闭语句连接对象
	 * 
	 * @throws SQLException
	 */
	private void closeResultSet(ResultSet rs) throws SQLException {
		if (rs != null) {
			Statement ps = rs.getStatement();
			rs.close();
			closeStatement(ps);
		}
	}

	@Override
	public Object executeScalar(String sql, Object... params)
			throws DatabaseExecuteFailedException {
		Connection conn = null;
		PreparedStatement preStatement = null;
		ResultSet rs = null;
		Object obj = null;

		try {
			conn = getConnection();
			preStatement = conn.prepareStatement(sql);
			initPreparedStatement(preStatement, params);
			rs = preStatement.executeQuery();

			if (rs.next()) {
				obj = rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				closeResultSet(rs);
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("Close ResultSet failed, caused by -> " + e);
				throw new DatabaseExecuteFailedException(e);
			}
		}

		return obj;
	}

	@Override
	public void commit() throws DatabaseExecuteFailedException {
		try {
			getConnection().commit();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Commit transaction failed, caused by -> " + e);
			throw new DatabaseExecuteFailedException(e);
		}
	}

	@Override
	public void rollBack() throws DatabaseExecuteFailedException {
		try {
			getConnection().rollback();
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Rollback transaction failed, caused by -> " + e);
			throw new DatabaseExecuteFailedException(e);
		}
	}

	@Override
	public void close() throws DatabaseExecuteFailedException {
		if (!resultSetCache.isEmpty()) {
			try {
				for (ResultSet rs : resultSetCache) {
					closeResultSet(rs);
				}
				resultSetCache.clear();
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("Close ResultSet failed, caused by -> " + e);
				throw new DatabaseExecuteFailedException(e);
			}

			try {
				 closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("Close Connection failed, caused by -> " + e);
				throw new DatabaseExecuteFailedException(e);
			}
		}
	}

	/**
	 * 关闭连接
	 * @throws SQLException 
	 */
	private void closeConnection() throws SQLException {
		Connection conn = getConnection();
		
		if(conn != null){
			conn.commit();
			conn.close();
			conn = null;
		}
	}

	@Override
	public boolean testLink() {
		Connection conn = null;

		try {
			conn = getConnection();

			if (conn != null) {
				conn.close();
				return true;
			}

			return false;
		} catch (SQLException e) {
			logger.error("Connection the database failed...");
		}

		return false;
	}

	/**
	 * 创建连接对象的方法，交给具体的数据库Session实现
	 * 
	 * @return
	 */
	protected abstract Connection createConnection() throws SQLException;
}
